# !/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
@作者 杨阳
@时间 2018-06-01
@说明 数据库系统管理中表对应的实体类,
        t_user, t_user_role, t_group, t_role, t_role_resource
        DEL -1__user__resource_cache
@注意事项:
* is_authenticated ：是否被验证
* is_active ： 用户是否可以登录
* is_anonymous : 是否是匿名用户
* get_id() : 获得用户的id，并转换为 Unicode 类型
"""
import json

from flask_login import UserMixin, current_user
from sqlalchemy.sql.functions import current_time

from settings import USER_RESOURCES_KEY
from util import RedisUtil, LogUtil
from util import FileUtil
from util.JsonUtil import JsonEncoder, classToDict
from util.ListUtil import remove_duplicate_data
from util.StrUtil import get_uuid, change_none_to_empty_string, encode_md5
from util.ThreadPluginsInitUtil import db
from util.ThreadPluginsInitUtil import login_manager
from util.generation.generationutil import GenerationConstant


class TUserModel(db.Model, UserMixin):
    """
    用户表实体类
    """
    __tablename__ = "t_user"

    id = db.Column(db.String(36), primary_key=True, index=True, comment="主键id")
    code = db.Column(db.String(50), unique=True, nullable=False, index=True, comment="编号，不可以重复")
    name = db.Column(db.String(50), nullable=False, index=True, comment="真实姓名")
    regname = db.Column(db.String(50), unique=True, nullable=False, index=True, comment="登录名")
    sex = db.Column(db.Integer, nullable=False, index=True, comment="性别")
    password = db.Column(db.String(50), nullable=False, comment="密码，存储的是md5加密后的")
    status = db.Column(db.Integer, nullable=False, comment="状态 1.正常 0.锁定")  # 状态 1.正常 0.锁定
    group_id = db.Column(db.String(36),
                         db.ForeignKey("t_group.id"), nullable=True,
                         comment="所在机构,外键参照t_group.id字段")  # 所在机构,外键参照t_group.id字段
    group_ids = db.Column(db.String(500), nullable=True, comment="所在机构，包含上级机构")  # 所在机构，包含上级机构
    photo = db.Column(db.LargeBinary, nullable=True, comment="用户头像")  # 用户头像
    certificate_type = db.Column(db.String(10), nullable=True, comment="证件类型")  # 证件类型
    certificate = db.Column(db.String(50), nullable=True, comment="证件号码")  # 证件号码
    phone = db.Column(db.String(50), nullable=True, comment="电话")  # 电话
    email = db.Column(db.String(50), nullable=True, comment="邮箱")  # 邮箱
    comment = db.Column(db.String(500), nullable=True, comment="备注")  # 备注
    creator = db.Column(db.String(36), nullable=False, comment="创建人")  # 创建人
    create_time = db.Column(db.DateTime, nullable=True, comment="创建时间")  # 创建时间

    # 配置多对多用户与角色关联关系
    # secondary 要配置为关联表
    roles = db.relationship('TRoleModel',
                            secondary="t_user_role",
                            backref=db.backref('t_user.id', lazy='dynamic'), lazy='dynamic')

    def __repr__(self):
        return "<name:%s>" % self.name

    def keys(self):
        LogUtil.info("TUserModel keys method................")
        user_keys = ('id', 'code', 'name', 'regname', 'sex', 'password', 'status',
                     'group_id', 'group_ids', 'photo', 'certificate_type', 'certificate',
                     'phone', 'email', 'comment', 'creator', 'create_time')
        LogUtil.info("TUserModel keys:{}".format(user_keys))
        LogUtil.info("TUserModel keys method................")
        return user_keys

    def __getitem__(self, item):
        return getattr(self, item)

    @login_manager.user_loader
    def user_loader(id):
        user = TUserModel.query.filter_by(id=id).first()
        return user

    @staticmethod
    def get_user_byId(id):
        """
        根据用户id查询用户
        :param id:  用户id
        :return:    用户对象
        """
        user = TUserModel.query.filter_by(id=id).first()
        return user

    @staticmethod
    def get_user_by_username(username):
        """
        根据登录名查询用户
        :param username: 对应数据库regname字段
        :return:
        """
        user = TUserModel.query.filter(TUserModel.regname == username).first()
        return user

    @staticmethod
    def check_code_exists(code):
        """
        查看用户编号是否已经存在
        :param code:    用户编号
        :return:
        """
        count = TUserModel.query.filter(TUserModel.code == code).count()
        return count

    @staticmethod
    def check_regname_exists(regname):
        """
        查看用户登录帐号是否已经存在
        :param regname:    用户帐号
        :return:
        """
        count = TUserModel.query.filter(TUserModel.regname == regname).count()
        return count

    @staticmethod
    def check_certificate_exists(certificate):
        """
        查看用户证件号是否已经存在
        :param regname:    用户帐号
        :return:
        """
        count = TUserModel.query.filter(TUserModel.certificate == certificate).count()
        return count

    @staticmethod
    def check_email_exists(email):
        """
        查看用户邮箱是否已经存在
        :param regname:    用户帐号
        :return:
        """
        count = TUserModel.query.filter(TUserModel.email == email).count()
        return count

    @staticmethod
    def check_phone_exists(phone):
        """
        查看用户电话是否已经存在
        :param regname:    用户帐号
        :return:
        """
        count = TUserModel.query.filter(TUserModel.phone == phone).count()
        return count

    @staticmethod
    def delete_user(id):
        try:
            user = user = TUserModel.query.filter_by(id=id).first()
            db.session.delete(user)
            db.session.commit()
            result = {"success": True, "msg": "删除成功!"}
        except Exception as err:
            LogUtil.info("删除用户异常：{}".format(err))
            db.session.rollback()
            result = {"success": False, "msg": "删除失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def import_user(file_path):
        """
        读取excel内容，批量导入用户
        :param file_path:   excel文件全路径，包含文件名
        :return:
        """

        row_list = []

        # 读取文件
        if file_path.endswith("xlsx"):
            LogUtil.info("读取2007版本的文档.....")
            row_list = FileUtil.read_xlsx_excel(file_path)
        else:
            LogUtil.info("读取2003版本的文档.....")
            row_list = FileUtil.read_xls_excel(file_path)

        for row in row_list:
            LogUtil.info(row)
            code = row[0]
            regname = row[1]
            name = row[2]
            sex = row[3]
            group_code = row[4]
            certificate_type = row[5]
            certificate = row[6]
            phone = row[7]
            email = row[8]
            group_id = ''

            # 判断用户编号是否已经存在,如果存在，那么就增加到错误信息里，不可以揷入
            code_exists = TUserModel.check_code_exists(code)
            if code_exists > 0:
                result = json.dumps({"success": False, "msg": "用户:{}重复!".format(code)})
                db.session.rollback()
                return result

            regname_exists = TUserModel.check_regname_exists(regname)
            if regname_exists > 0:
                result = json.dumps({"success": False, "msg": "用户帐号:{}重复!".format(regname)})
                db.session.rollback()

            if certificate is not None and certificate != '':
                certificate_exists = TUserModel.check_certificate_exists(certificate)
                if certificate_exists > 0:
                    result = json.dumps({"success": False, "msg": "证件号:{}重复!".format(certificate)})
                    db.session.rollback()

            if email is not None and email != '':
                email_exists = TUserModel.check_email_exists(email)
                if email_exists > 0:
                    result = json.dumps({"success": False, "msg": "邮箱:{}重复!".format(email)})
                    db.session.rollback()

            if phone is not None and phone != '':
                phone_exists = TUserModel.check_phone_exists(phone)
                if phone_exists > 0:
                    result = json.dumps({"success": False, "msg": "邮箱:{}重复!".format(phone)})
                    db.session.rollback()

            group = TGroupModel.get_group_bycode(group_code)
            if not group:
                result = json.dumps({"success": False, "msg": "机构编号:{}不存在!".format(group_code)})
                db.session.rollback()
            else:
                group_id = group.id

            try:
                model = TUserModel()
                model.id = get_uuid()
                model.regname = regname
                model.name = name
                model.code = code
                model.password = encode_md5(regname)
                model.sex = sex
                model.group_id = group_id
                model.group_ids = change_none_to_empty_string('')
                model.certificate_type = change_none_to_empty_string(certificate_type)
                model.certificate = change_none_to_empty_string(certificate)
                model.status = 1
                model.phone = change_none_to_empty_string(phone)
                model.email = change_none_to_empty_string(email)
                model.creator = current_user.id
                model.create_time = db.cast(current_time(), db.DateTime)
                model.comment = "这个人很懒，什么都没有留下!"
                db.session.add(model)
            except Exception as err:
                db.session.rollback()
                LogUtil.info("揷入用户异常:{}".format(err))

                result = json.dumps({"success": False, "msg": "导入用户异常:{}!".format(err)})
                db.session.rollback()
                return result

        db.session.commit()
        result = json.dumps({"success": True, "msg": "导入用户成功!"})
        return result

    @staticmethod
    def add_user(regname, name, code, sex, group_id, certificate_type, certificate, phone, email, comment):
        try:
            if regname and "" != regname:
                regname_exists = TUserModel.check_regname_exists(regname)
                if regname_exists > 0:
                    return json.dumps({"success": False, "msg": "用户帐号:{}重复,请重新输入!".format(regname)})

            if code and "" != code:
                code_exists = TUserModel.check_code_exists(code)
                if code_exists > 0:
                    return json.dumps({"success": False, "msg": "用户编号:{}重复,请重新输入!".format(code)})

            if certificate and "" != certificate:
                certificate_exists = TUserModel.check_certificate_exists(certificate)
                if certificate_exists > 0:
                    return json.dumps({"success": False, "msg": "证件号码:{}重复,请重新输入!".format(certificate)})

            if phone and "" != phone:
                phone_exists = TUserModel.check_phone_exists(phone)
                if phone_exists > 0:
                    return json.dumps({"success": False, "msg": "手机号码:{}重复,请重新输入!".format(phone)})

            if email and "" != email:
                email_exists = TUserModel.check_email_exists(email)
                if email_exists > 0:
                    return json.dumps({"success": False, "msg": "电子邮件:{}重复,请重新输入!".format(email)})

            model = TUserModel()
            model.id = get_uuid()
            model.regname = regname
            model.name = name
            model.code = code
            model.password = encode_md5(regname)
            model.sex = sex
            model.group_id = group_id
            model.group_ids = change_none_to_empty_string('')
            model.certificate_type = change_none_to_empty_string(certificate_type)
            model.certificate = change_none_to_empty_string(certificate)
            model.status = 1
            model.phone = change_none_to_empty_string(phone)
            model.email = change_none_to_empty_string(email)
            model.creator = current_user.id
            model.create_time = db.cast(current_time(), db.DateTime)
            remark = change_none_to_empty_string(comment)
            if remark == '':
                remark = "这个人很懒，什么都没有留下!"
            model.comment = remark
            db.session.add(model)
            db.session.commit()
            result = {"success": True, "msg": "增加用户成功!"}
            return json.dumps(result)
        except Exception as e:
            LogUtil.info("增加用户异常:{}", format(e))
            result = {"success": False, "msg": "增加用户失败,请重试!"}
            return json.dumps(result)
        finally:
            pass

    @staticmethod
    def update_user(id, name, code, sex, certificate_type, certificate, phone, email, comment):
        try:
            if code and "" != code:
                code_exists = TUserModel.query.filter(TUserModel.code == code).filter(TUserModel.id != id).count()
                if code_exists > 0:
                    return json.dumps({"success": False, "msg": "用户编号:{}重复,请重新输入!".format(code)})

            if certificate and "" != certificate:
                certificate_exists = TUserModel.query. \
                    filter(TUserModel.certificate == certificate).filter(TUserModel.id != id).count()
                if certificate_exists > 0:
                    return json.dumps({"success": False, "msg": "证件号码:{}重复,请重新输入!".format(certificate)})

            if phone and "" != phone:
                phone_exists = TUserModel.query.filter(TUserModel.phone == phone).filter(TUserModel.id != id).count()
                if phone_exists > 0:
                    return json.dumps({"success": False, "msg": "手机号码:{}重复,请重新输入!".format(phone)})

            if email and "" != email:
                email_exists = TUserModel.query.filter(TUserModel.email == email).filter(TUserModel.id != id).count()
                if email_exists > 0:
                    return json.dumps({"success": False, "msg": "电子邮件:{}重复,请重新输入!".format(email)})

            model = TUserModel.get_user_byId(id)
            model.name = name
            model.code = code
            model.sex = sex
            model.certificate_type = change_none_to_empty_string(certificate_type)
            model.certificate = change_none_to_empty_string(certificate)
            model.phone = change_none_to_empty_string(phone)
            model.email = change_none_to_empty_string(email)
            remark = change_none_to_empty_string(comment)
            model.comment = remark
            db.session.commit()
            result = {"success": True, "msg": "修改用户成功!"}
            return json.dumps(result)
        except Exception as e:
            LogUtil.info("修改用户异常：{}".format(e))
            result = {"success": True, "msg": "修改用户失败，高重试!"}
            return json.dumps(result)
        finally:
            pass

    @staticmethod
    def get_user_grid(current_page=1, group_id=-1, search_name='',
                      search_sex='', search_start_data='', search_end_data=''):
        """
        查询用户的数据表格，带分页及查询功能
        :param current_page: 当前页
        :return:
        """
        # 查询用户列表
        filters = {
            1 == 1
        }

        if group_id is not None and group_id != '':
            filters.add(TUserModel.group_id == group_id)

        if search_name is not None and search_name != '':
            filters.add(TUserModel.name == search_name)

        if search_sex is not None and search_sex != '':
            filters.add(TUserModel.sex == search_sex)

        if search_start_data is not None and search_start_data != '':
            filters.add(TUserModel.create_time >= search_start_data)

        if search_end_data is not None and search_end_data != '':
            filters.add(TUserModel.create_time <= search_end_data)

        user_list = db.session.query(TUserModel.id,
                                     TUserModel.regname,
                                     TUserModel.name,
                                     TUserModel.create_time,
                                     TUserModel.email,
                                     TUserModel.sex,
                                     TGroupModel.name,
                                     TUserModel.status) \
            .filter(*filters) \
            .order_by(TUserModel.create_time.desc()) \
            .join(TGroupModel, TGroupModel.id == TUserModel.group_id) \
            .limit(10) \
            .offset((int(current_page) - 1) * 10)

        # 查询总记录数
        total = db.session.query(TUserModel.id) \
            .filter(*filters) \
            .join(TGroupModel, TGroupModel.id == TUserModel.group_id) \
            .count()

        list = []
        for user in user_list:
            list.append({
                "id": user[0],
                "regname": user[1],
                "name": user[2],
                "create_time": user[3],
                "email": user[4],
                "sex": user[5],
                "gname": user[6],
                "status": user[7]
            })

        retu = {"success": True, "grid": list, "total_count": total}
        result = json.dumps(retu, cls=JsonEncoder)
        return result

    @staticmethod
    def export_user_excel():
        """
        导出用户数据为excel
        列：用户编号,登录帐号,真实姓名,性别,机构编号,机构名称,证件类型,证件号码,电话,邮箱,用户状态,创建时间
        :return:
        """
        header_list = ['用户编号', '登录帐号', '真实姓名', '性别', '机构编号', '机构名称'
            , '证件类型', '证件号码', '电话', '邮箱', '创建时间']
        user_list = db.session.query(TUserModel.code,
                                     TUserModel.regname,
                                     TUserModel.name,
                                     TUserModel.sex,
                                     TGroupModel.code,
                                     TGroupModel.name,
                                     TUserModel.certificate_type,
                                     TUserModel.certificate,
                                     TUserModel.phone,
                                     TUserModel.email,
                                     TUserModel.create_time) \
            .order_by(TUserModel.create_time.desc()) \
            .all()

        file_name = FileUtil.write_xlsx_excel(header_list=header_list, row_list=user_list)
        return file_name

    @staticmethod
    def get_user_resources(id):
        """
        获得用户的所有权限
        :return:
        """
        user = TUserModel.query.filter(TUserModel.id == id).first()
        user_roles = user.roles.all()
        redis_key = id + USER_RESOURCES_KEY

        LogUtil.info("redis key:{}".format(redis_key))

        resource_list = RedisUtil.get_value(redis_key)

        LogUtil.info("resource_list:{}".format(resource_list))

        if not resource_list:
            resource_list = []
            for role in user_roles:

                role_sql = r'''
                    SELECT * FROM v_user_resources
                        WHERE role_id = :1 AND user_id = :2 AND parentid = :3
                    ORDER BY tindex
                '''
                role_resources = db.session.execute(role_sql, {"1": role.id, "2": id, "3": -1})

                for resource in role_resources:
                    resourceid = resource[2]
                    # 根据一级菜单的id查询二级菜单，即parentid = resourceid的
                    two_list = []

                    two = db.session.execute(role_sql, {"1": role.id, "2": id, "3": resourceid})
                    for res in two:
                        two_list.append({
                            "id": res[2],
                            "name": res[4],
                            "url": res[1],
                            "icon": res[7]
                        })

                    resource_list.append({
                        "id": resource[2],
                        "name": resource[4],
                        "url": resource[1],
                        "icon": resource[7],
                        "children": two_list
                    })

            # 设置缓存
            RedisUtil.set_value(redis_key, resource_list)
        else:
            resource_list = RedisUtil.get_value(redis_key)

        retu = {"success": True, "resources": resource_list}
        result = json.dumps(retu, cls=JsonEncoder)
        return result


class TGroupModel(db.Model):
    """
    机构表实体类
    """
    __tablename__ = "t_group"

    id = db.Column(db.String(36), primary_key=True, index=True, comment="主键id")
    name = db.Column(db.String(50), nullable=False, index=True, comment="机构名称")
    code = db.Column(db.String(50), nullable=False, index=True, comment="机构编号,不可以重复")
    parentid = db.Column(db.String(36), nullable=True, index=True, comment="父id")
    tindex = db.Column(db.Integer, nullable=False, comment="顺序")
    rootid = db.Column(db.String(50), nullable=False, index=True, comment="根节点id，好没啥鸟用啊，到时候看吧")
    level = db.Column(db.Integer, nullable=False, comment="节点的层级")

    l1 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l2 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l3 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l4 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l5 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l6 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l7 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l8 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l9 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")
    l10 = db.Column(db.String(36), comment="当前节点与当前节点的所有父节点")

    creator = db.Column(db.String(36), nullable=False)  # 创建人
    full_name = db.Column(db.String(1000), nullable=False)  # 机构全名

    users = db.relationship("TUserModel", backref="t_group", lazy=True)  # 表示可以通过t_user表中的group_id获得机构下的所有用户

    def __repr__(self):
        return "<name:%s>" % self.name

    @staticmethod
    def get_group_bycode(code):
        """
        根据机构编号获得机构
        :param code:    机构编号
        :return:        TGroupModel
        """
        group = TGroupModel.query.filter(TGroupModel.code == code).first()
        return group

    @staticmethod
    def get_group_tree(parentid=-1):
        """
        异步查询机构树
        :param parentid: 默认等于-1，根节点parentid即等于-1
        :return:
        """
        if parentid is None:
            parentid = -1

        list = []
        sql = '''
            SELECT id, name, 
                (SELECT COUNT(*) FROM t_group t2 WHERE t1.id = t2.parentid) hasChildren 
            FROM t_group t1 WHERE t1.parentid = '{}'
        '''.format(parentid)
        group_list = db.session.execute(sql)

        for group in group_list:
            id = group[0]
            title = group[1]
            # 大于零表示有子节点
            hasChildren = group[2]

            if hasChildren > 0:
                list.append({
                    "id": id,
                    "title": title,
                    "loading": False,
                    "children": [],
                    "expand": False
                })
            else:
                list.append({
                    "id": id,
                    "title": title
                })

        result = json.dumps(list, cls=JsonEncoder)
        return result


class TRoleModel(db.Model):
    """
    角色表实体类
    """
    __tablename__ = "t_role"
    id = db.Column(db.String(36), primary_key=True, index=True, comment="主键id")
    name = db.Column(db.String(200), nullable=False, index=True, comment="角色名称")
    comment = db.Column(db.String(500), nullable=True, comment="角色描述")
    creator = db.Column(db.String(36), db.ForeignKey("t_user.id"), nullable=False, comment="创建人")  # 创建人
    create_time = db.Column(db.DateTime, nullable=True, comment="创建时间")  # 创建时间

    # 配置权限与权限的关系
    # secondary 要配置为关联表 t_role_resource
    resources = db.relationship('TResourceModel', secondary="t_role_resource",
                                backref=db.backref('t_resource.id', lazy='dynamic'), lazy='dynamic')

    @staticmethod
    def get_role_byId(role_id):
        """
        根据角色id获取角色
        :param role_id:     角色idl
        :return:
        """
        role = TRoleModel.query.filter(TRoleModel.id == role_id).first()
        result = {"success": True, "role": classToDict(role)}
        return json.dumps(result, cls=JsonEncoder)

    @staticmethod
    def get_role_resource_tree(role_id, user_id):
        """
        获得角色包含的权限，并且默认选中当前用户拥有的权限
        :param role_id:     角色id
        :return:
        """
        resultList = []

        #   注：两个子查询
        #       一.第一个子查询，如果大于零，说明用户包含的权限，需要选中，
        #       二.第一个子查询，如果大于零，表示当前的节点是根节点
        sql = '''
            SELECT *, (
                    SELECT count(1) FROM t_role_resource vur
                    WHERE vur.role_id = :1
                    AND vur.resource_id = vrr.id
                ) isHaveResource,
                (
                    SELECT COUNT(1) FROM t_resource tr
                    WHERE tr.id = vrr.id
                    AND tr.parentid = -1
                ) isRootNode
            FROM t_resource vrr WHERE vrr.parentid = :2
        '''
        # 获得角色的根权限
        role_root_resources = db.session.execute(sql, {"1": role_id, "2": "-1"})

        for root in role_root_resources:
            resource_id = root[0]
            # 装子节点的集合
            children_list = []
            # 查询子节点
            role_resources = db.session.execute(sql, {"1": role_id, "2": resource_id})
            for i, node in enumerate(role_resources):
                children_list.append({
                    "id": node[0],
                    "title": node[1],
                    "icon": node[5],
                    "isChecked": node[8]
                })

            expand = False
            if children_list and len(children_list) > 0:
                expand = True

            resultList.append({
                "id": resource_id,
                "title": root[1],
                "expand": expand,
                "icon": root[5],
                "children": children_list,
                "isChecked": root[8]
            })
        result = json.dumps(resultList, cls=JsonEncoder)
        LogUtil.info("返回结果：{}".format(result))
        return result

    @staticmethod
    def add_role(name, comment, resource_ids):
        """
        增加角色
        :param name:            角色名称
        :param comment:         角色备注
        :param resource_ids:    角色中包含的权限id字符串，以","分隔
        :return:
        """
        try:
            role = TRoleModel()
            role.id = get_uuid()
            role.name = name
            if not comment:
                comment = ""
            role.comment = comment
            role.create_time = db.cast(current_time(), db.DateTime)
            role.creator = current_user.id

            # 将新的关系添加上
            for resource_id in resource_ids.split(","):
                if resource_id and '' != resource_id:
                    resource = TResourceModel.query.filter(TResourceModel.id == resource_id).first()
                    role.resources.append(resource)

            db.session.add(role)
            db.session.commit()
            result = {"success": True, "msg": "增加角色成功!"}

            RedisUtil.delete_key_by_pattern("*" + USER_RESOURCES_KEY)

        except Exception as err:
            LogUtil.info("增加角色异常：{}".format(err))
            db.session.rollback()
            result = {"success": False, "msg": "增加角色失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def update_role(id, name, comment, resource_ids):
        try:
            role = TRoleModel.query.filter(TRoleModel.id == id).first()
            role.name = name
            if not comment:
                comment = ""
            role.comment = comment

            # 清除掉角色下的所有权限
            sql = '''
                delete from t_role_resource where role_id = :1
            '''
            db.session.execute(sql, {"1": id})

            # 将角色与用户选中的权限相关联
            resource_array = remove_duplicate_data(resource_ids.split(","))
            for resource_id in resource_array:
                if resource_id and '' != resource_id:
                    resource = TResourceModel.query.filter(TResourceModel.id == resource_id).first()
                    LogUtil.info("权限id:{}".format(resource_id))
                    role.resources.append(resource)

            db.session.commit()
            result = {"success": True, "msg": "修改角色成功!"}

            RedisUtil.delete_key_by_pattern("*" + USER_RESOURCES_KEY)

        except Exception as err:
            db.session.rollback()
            LogUtil.info("修改角色异常：{}".format(err))
            result = {"success": False, "msg": "修改角色失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def delete_role(id):
        try:
            result = {}
            # 查看当前角色是否与用户关联
            sql = '''
                SELECT * FROM v_user_role WHERE id = '{}'
            '''.format(id)
            role = db.session.execute(sql)
            if role and role.rowcount > 0:
                result = {"success": False, "msg": "删除角色失败，角色已经与用户关联!"}
            else:
                obj = TRoleModel.query.filter(TRoleModel.id == id).first()
                db.session.delete(obj)
                db.session.commit()
                result = {"success": True, "msg": "删除角色成功!"}
        except Exception as err:
            LogUtil.info("删除角色异常：{}".format(err))
            db.session.rollback()
            result = {"success": False, "msg": "删除有色失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def get_role_grid(current_page=1, search_name='', search_start_data='', search_end_data=''):
        """
        查询用户的数据表格，带分页及查询功能
        :param search_name:         通过名称查询
        :param search_end_data:     创建时间开始
        :param search_start_data:   创建时间结束
        :param current_page:        当前页
        :return:
        """
        # 查询角色列表
        filters = {
            1 == 1
        }

        if search_name is not None and search_name != '':
            filters.add(TRoleModel.name.like("%" + search_name + "%"))

        if search_start_data is not None and search_start_data != '':
            filters.add(TRoleModel.create_time >= search_start_data)

        if search_end_data is not None and search_end_data != '':
            filters.add(TRoleModel.create_time <= search_end_data)

        obj_list = db.session.query(TRoleModel.id,
                                    TRoleModel.name,
                                    TRoleModel.create_time,
                                    TRoleModel.creator,
                                    TRoleModel.comment,
                                    TUserModel.name) \
            .filter(*filters) \
            .join(TUserModel, TRoleModel.creator == TUserModel.id) \
            .order_by(TRoleModel.create_time.desc()) \
            .limit(10) \
            .offset((int(current_page) - 1) * 10)

        # 查询总记录数
        total = db.session.query(TRoleModel.id) \
            .filter(*filters) \
            .count()

        list = []
        for obj in obj_list:
            list.append({
                "id": obj[0],
                "name": obj[1],
                "create_time": obj[2],
                "creator": obj[5],
                "comment": obj[4]
            })

        retu = {"success": True, "grid": list, "total_count": total}
        result = json.dumps(retu, cls=JsonEncoder)
        return result


class TResourceModel(db.Model):
    """
    权限表实体类
    """
    __tablename__ = "t_resource"
    id = db.Column(db.String(36), primary_key=True, index=True, comment="主键id")
    name = db.Column(db.String(200), nullable=False, index=True, comment="权限名称")
    parentid = db.Column(db.String(36), nullable=False, index=True, comment="父节点id")
    tindex = db.Column(db.Integer, nullable=False, comment="父节点id")
    url = db.Column(db.String(500), nullable=False, comment="菜单的访问地址，其实对应的就是.vue的名称")
    icon = db.Column(db.String(255), nullable=False, comment="权限菜单图标，必须是iview中的图标，其它的不认识")
    creator = db.Column(db.String(36), nullable=False, comment="创建人")  # 创建人
    create_time = db.Column(db.DateTime, nullable=True, comment="创建时间")  # 创建时间

    @staticmethod
    def delete_resource(id):
        # 查看当前节点是否有子节点,有子节点不允许删除
        count = TResourceModel.query.filter(TResourceModel.parentid == id).count()
        if count > 0:
            result = {"success": False, "msg": "当前节点包含子节点，不允许删除!"}
            return json.dumps(result)
        else:
            resource = TResourceModel.query.filter(TResourceModel.id == id).first()
            db.session.delete(resource)
            db.session.commit()
            result = {"success": True, "msg": "删除成功!"}

            RedisUtil.delete_key_by_pattern("*" + USER_RESOURCES_KEY)

            return json.dumps(result)

    @staticmethod
    def get_resource_byid(id):
        resource = TResourceModel.query.filter(TResourceModel.id == id).first()
        resource_dict = classToDict(resource)
        result = json.dumps({"success": True, "resource": resource_dict}, cls=JsonEncoder)
        return result

    @staticmethod
    def add_resource(name, tindex, url, icon, parentid):
        """
        新増权限
        :param id:          权限id
        :param name:        权限名称
        :param tindex:      权限排序
        :param url:         权限url
        :param icon:        权限图标
        :param parentid:    父权限id
        :return:
        """
        try:
            resource = TResourceModel()
            resource.id = get_uuid()
            resource.name = name
            resource.tindex = tindex
            resource.url = url
            resource.icon = icon
            resource.parentid = parentid
            resource.create_time = db.cast(current_time(), db.DateTime)
            resource.creator = current_user.id
            db.session.add(resource)
            db.session.commit()
            result = {"success": True, "msg": "增加权限成功!"}

            RedisUtil.delete_key_by_pattern("*" + USER_RESOURCES_KEY)

        except Exception as err:
            LogUtil.info("增加权限异常：{}".format(err))
            db.session.rollback()
            result = {"success": False, "msg": "增加权限失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def update_resource(id, name, tindex, url, icon):
        """
        更新权限信息
        :param id:      权限id
        :param name:    权限名称
        :param tindex:  权限排序
        :param url:     权限url
        :param icon:    权限图标
        :return:
        """
        try:
            resource = TResourceModel.query.filter(TResourceModel.id == id).first()
            resource.name = name
            resource.tindex = tindex
            resource.url = url
            resource.icon = icon
            db.session.commit()
            result = {"success": True, "msg": "修改权限成功!"}

            RedisUtil.delete_key_by_pattern("*" + USER_RESOURCES_KEY)

        except Exception as err:
            LogUtil.info("修改权限异常：{}".format(err))
            db.session.rollback()
            result = {"success": False, "msg": "修改权限失败，请重试!"}
        finally:
            pass
        return json.dumps(result)

    @staticmethod
    def get_resource_tree(parentid=-1):
        """
        异步查询机构树
        :param parentid: 默认等于-1，根节点parentid即等于-1
        :return:
        """
        if parentid is None:
            parentid = -1

        list = []
        sql = '''
                SELECT id, name, url, tindex,
                    (SELECT COUNT(*) FROM t_resource t2 WHERE t1.id = t2.parentid) hasChildren, icon 
                FROM t_resource t1 WHERE t1.parentid = '{}' order by tindex
            '''.format(parentid)
        resource_list = db.session.execute(sql)

        for i, resource in enumerate(resource_list):
            id = resource[0]
            title = resource[1]
            icon = resource[5]
            # 大于零表示有子节点
            hasChildren = resource[4]

            if hasChildren > 0:
                list.append({
                    "id": id,
                    "title": title,
                    "loading": False,
                    "children": [],
                    "expand": False,
                    "index": i,
                    "icon": icon
                })
            else:
                list.append({
                    "id": id,
                    "title": title,
                    "index": i,
                    "parentid": "1",
                    "icon": icon
                })

        result = json.dumps(list, cls=JsonEncoder)
        return result


# 角色与权限关联表
# 关联表不需要定义实体类，SQLAlchemy会自动接管这个表
TRoleResource = db.Table('t_role_resource',
                         # 外键,关联t_role的id
                         db.Column('role_id', db.String(36),
                                   db.ForeignKey("t_role.id"), primary_key=True, index=True),
                         # 外键,关联t_resource的id
                         db.Column('resource_id', db.String(36),
                                   db.ForeignKey("t_resource.id"), primary_key=True, index=True)
                         )

# 用户与角色关联表
# 关联表不需要定义实体类，SQLAlchemy会自动接管这个表
TUserRole = db.Table('t_user_role',
                     # 外键,关联t_user的id
                     db.Column('user_id', db.String(36),
                               db.ForeignKey("t_user.id"), primary_key=True, index=True),
                     # 外键,关联t_role的id
                     db.Column('role_id', db.String(36),
                               db.ForeignKey("t_role.id"), primary_key=True, index=True)
                     )


class GenerationModel():

    @staticmethod
    def get_database_table_grid(current_page, search_name):
        sql = '''
            SELECT TABLE_NAME, TABLE_COMMENT
	            FROM INFORMATION_SCHEMA. TABLES
            WHERE TABLE_SCHEMA = '%s' AND TABLE_TYPE = 'base table' %s
            ORDER BY TABLE_NAME LIMIT %s, 10
        '''

        count_sql = '''
            SELECT COUNT(1) COU
	            FROM INFORMATION_SCHEMA. TABLES
            WHERE TABLE_SCHEMA = '%s' AND TABLE_TYPE = 'base table' %s
        '''

        new_sql = sql % ("learning", "", (int(current_page) - 1) * 10)
        new_count_sql = count_sql % ("learning", " ")

        if search_name is not None and search_name != "":
            new_sql = sql % (GenerationConstant.db_database,
                             " AND TABLE_NAME like '%" + search_name + "%'",
                             (int(current_page) - 1) * 10)

            new_count_sql = count_sql % (GenerationConstant.db_database,
                                         " AND TABLE_NAME = '" + search_name + "'")

        grid = db.session.execute(new_sql)
        LogUtil.info("表格数据：{}".format(grid))

        total = (db.session.execute(new_count_sql).fetchone())[0]
        LogUtil.info("总数据：{}".format(total))

        list = []
        for user in grid:
            list.append({
                "name": user[0],
                "comment": user[1]
            })

        retu = {"success": True, "grid": list, "total_count": total}
        result = json.dumps(retu, cls=JsonEncoder)
        return result

    @staticmethod
    def get_columns_grid(table_name):
        """
        根据表名获得表中的所有列
        :param table_name:      表名
        :return: 
        """
        sql = '''
            SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, IS_NULLABLE,
                IFNULL(CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION) CHARACTER_MAXIMUM_LENGTH
            FROM information_schema.COLUMNS
            WHERE table_name = '{}'
            AND TABLE_SCHEMA = '{}'
        '''.format(table_name, GenerationConstant.db_database)

        grid = db.session.execute(sql)

        list = []
        for user in grid:
            list.append({
                "name": user[0],
                "datatype": user[1],
                "comment": user[2],
                "nullable": user[3],
                "length": user[4]
            })

        retu = {"success": True, "grid": list}
        result = json.dumps(retu, cls=JsonEncoder)
        return result
